﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Utility.Helper;

namespace BLL
{
    public class Lc_TransactionBLL : BaseBLL
    {
        public Lc_TransactionBLL()
        {
        }

        public Lc_TransactionBLL(Service service)
        {
            this.Service = service;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <returns></returns>
        public int Insert(Lc_Transaction model, string operName)
        {
            try
            {
                model.N_TRANSACTION_ID = GetSequence("N_TRANSACTION_ID");
                //添加节点的sql
                string sql = string.Format(@"INSERT INTO LC_TRANSACTION
                                              (N_TRANSACTION_ID,
                                               N_PROCESS_ID,
                                               N_QUEUE_ID,
                                               N_BUSINESS_ID,
                                               N_TRANSACTION_STATE,
                                               N_TRANSACTION_COMMENT,
                                               C_TRANSACTOR_ID,
                                               D_TRANSACTION_TIME_BEGIN,
                                               D_TRANSACTION_TIME_END,
                                               D_TRANSACTION_TIME_DUE,
                                               N_TRANSACTION_DUE_STATE,
                                               C_TRANSACTOR_SIGNATURE,
                                               N_LAST_BACE_TRANSACTION_ID)
                                            VALUES
                                              ('{12}',
                                               '{0}',
                                               '{1}',
                                               '{2}',
                                               '{3}',
                                               '{4}',
                                               '{5}',
                                               to_date('{6}','yyyy-MM-dd HH24:Mi:ss'),
                                               to_date('{7}','yyyy-MM-dd HH24:Mi:ss'),
                                               to_date('{8}','yyyy-MM-dd HH24:Mi:ss'),
                                               '{9}',
                                               '{10}',
                                               '{11}')",
                                            model.N_PROCESS_ID,
                                            model.N_QUEUE_ID,
                                            model.N_business_id,
                                            model.N_TRANSACTION_STATE,
                                            model.C_TRANSACTION_COMMENT,
                                            model.C_TRANSACTOR_ID,
                                            model.D_TRANSACTION_TIME_BEGIN,
                                            model.D_TRANSACTION_TIME_END,
                                            model.D_TRANSACTION_TIME_DUE,
                                            model.N_TRANSACTION_DUE_STATE,
                                            model.C_TRANSACTOR_SIGNATURE,
                                            model.N_LAST_BACE_TRANSACTION_ID,
                                            model.N_TRANSACTION_ID);

                //执行sql并接收返回结果
                int count = NoQuery(sql, operName);
                //返回结果
                return count;
            }
            catch (Exception)
            {
                throw;
            }
        }

        #region 公司汇总新增代码

        public bool Insert(Lc_Transaction entity)
        {
            var sqlp = InsertSqlP(entity);
            var count = NoQuery(sqlp.Item1, sqlp.Item2);
            return count > 0 ? true : false;
        }

        public Tuple<string, List<Tuple<string, string, string>>> InsertSqlP(Lc_Transaction entity)
        {
            entity.N_TRANSACTION_ID = GetSequence("N_TRANSACTION_ID");
            var sql = @"INSERT INTO LC_TRANSACTION(
	N_TRANSACTION_ID,
	N_PROCESS_ID,
	N_QUEUE_ID,
	N_BUSINESS_ID,
	N_TRANSACTION_STATE,
	N_TRANSACTION_COMMENT,
	C_TRANSACTOR_ID,
	D_TRANSACTION_TIME_BEGIN,
	D_TRANSACTION_TIME_END,
	D_TRANSACTION_TIME_DUE,
	N_TRANSACTION_DUE_STATE,
	C_TRANSACTOR_SIGNATURE,
	N_LAST_BACE_TRANSACTION_ID)
VALUES(
	:N_TRANSACTION_ID,
	:N_PROCESS_ID,
	:N_QUEUE_ID,
	:N_BUSINESS_ID,
	:N_TRANSACTION_STATE,
	:C_TRANSACTION_COMMENT,
	:C_TRANSACTOR_ID,
	:D_TRANSACTION_TIME_BEGIN,
	:D_TRANSACTION_TIME_END,
	:D_TRANSACTION_TIME_DUE,
	:N_TRANSACTION_DUE_STATE,
	:C_TRANSACTOR_SIGNATURE,
	:N_LAST_BACE_TRANSACTION_ID)";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":N_TRANSACTION_ID", entity.N_TRANSACTION_ID, "int");
            parms.Add(":N_PROCESS_ID", entity.N_PROCESS_ID, "int");
            parms.Add(":N_QUEUE_ID", entity.N_QUEUE_ID, "int");
            parms.Add(":N_BUSINESS_ID", entity.N_business_id, "int");
            parms.Add(":N_TRANSACTION_STATE", entity.N_TRANSACTION_STATE, "int");
            parms.Add(":C_TRANSACTION_COMMENT", entity.C_TRANSACTION_COMMENT, "string");
            parms.Add(":C_TRANSACTOR_ID", entity.C_TRANSACTOR_ID, "string");
            parms.Add(":D_TRANSACTION_TIME_BEGIN", entity.D_TRANSACTION_TIME_BEGIN, "datetime");
            parms.Add(":D_TRANSACTION_TIME_END", entity.D_TRANSACTION_TIME_END, "datetime");
            parms.Add(":D_TRANSACTION_TIME_DUE", entity.D_TRANSACTION_TIME_DUE, "datetime");
            parms.Add(":N_TRANSACTION_DUE_STATE", entity.N_TRANSACTION_DUE_STATE, "int");
            parms.Add(":C_TRANSACTOR_SIGNATURE", entity.C_TRANSACTOR_SIGNATURE, "string");
            parms.Add(":N_LAST_BACE_TRANSACTION_ID", entity.N_LAST_BACE_TRANSACTION_ID, "int");
            return new Tuple<string, List<Tuple<string, string, string>>>(sql, parms);
        }

        #endregion 公司汇总新增代码

        public DataTable GetAllInfo(string Type, object param)
        {
            string sql = string.Format(@"SELECT
    lcn.C_NODE_NAME,
    CASE lct.N_TRANSACTION_STATE
    WHEN  '0' THEN '正在办理'
    WHEN  '1' THEN '流转'
    WHEN  '2' THEN '回退'
    WHEN  '3' THEN '回跳'
    END as N_TRANSACTION_STATE,
    lct.C_TRANSACTOR_ID,
    lct.n_transaction_comment,
    lct.D_TRANSACTION_TIME_BEGIN,
    lct.D_TRANSACTION_TIME_DUE,
    lct.D_TRANSACTION_TIME_END,
    lct.C_TRANSACTOR_SIGNATURE
FROM
    THKQS.Lc_Transaction lct,
    THKQS.Lc_Queue lcq,
    THKQS.Lc_Node lcn
WHERE
    lct.N_QUEUE_ID = lcq.N_QUEUE_ID
    AND lcq.N_NODE_ID = lcn.N_NODE_ID");

            sql += " ORDER BY lct.N_TRANSACTION_ID DESC";
            DataTable dt = Query(sql, "流程管理", "查询流程详情", "", "");
            return dt;
        }

        public Lc_Transaction GetInfoById(string id)
        {
            string sql = "SELECT N_TRANSACTION_ID, N_PROCESS_ID, N_QUEUE_ID, N_BUSINESS_ID, N_TRANSACTION_STATE, N_TRANSACTION_COMMENT,C_TRANSACTOR_ID,D_TRANSACTION_TIME_BEGIN, D_TRANSACTION_TIME_END, D_TRANSACTION_TIME_DUE, N_TRANSACTION_DUE_STATE, C_TRANSACTOR_SIGNATURE, N_LAST_BACE_TRANSACTION_ID FROM LC_TRANSACTION WHERE N_TRANSACTION_ID=" + id;

            DataTable dt = Query(sql);
            Lc_Transaction model = new Lc_Transaction();

            if (dt != null && dt.Rows.Count > 0)
            {
                model.N_TRANSACTION_ID = decimal.Parse(dt.Rows[0]["N_TRANSACTION_ID"].ToString());
                model.N_PROCESS_ID = decimal.Parse(dt.Rows[0]["N_PROCESS_ID"].ToString());
                model.N_QUEUE_ID = decimal.Parse(dt.Rows[0]["N_QUEUE_ID"].ToString());
                model.N_business_id = decimal.Parse(dt.Rows[0]["N_BUSINESS_ID"].ToString());
                model.N_TRANSACTION_STATE = decimal.Parse(dt.Rows[0]["N_TRANSACTION_STATE"].ToString());
                model.C_TRANSACTION_COMMENT = dt.Rows[0]["N_TRANSACTION_COMMENT"].ToString();
                model.C_TRANSACTOR_ID = dt.Rows[0]["C_TRANSACTOR_ID"].ToString();
                model.D_TRANSACTION_TIME_BEGIN = DateTime.Parse(dt.Rows[0]["D_TRANSACTION_TIME_BEGIN"].ToString());
                model.D_TRANSACTION_TIME_END = DateTime.Parse(dt.Rows[0]["D_TRANSACTION_TIME_END"].ToString());
                model.D_TRANSACTION_TIME_DUE = DateTime.Parse(dt.Rows[0]["D_TRANSACTION_TIME_DUE"].ToString());
                model.N_TRANSACTION_DUE_STATE = decimal.Parse(dt.Rows[0]["N_TRANSACTION_DUE_STATE"].ToString());
                model.C_TRANSACTOR_SIGNATURE = dt.Rows[0]["C_TRANSACTOR_SIGNATURE"].ToString();
                model.N_LAST_BACE_TRANSACTION_ID = decimal.Parse(dt.Rows[0]["N_LAST_BACE_TRANSACTION_ID"].ToString());
                //model.N_TYPE = decimal.Parse(dt.Rows[0]["N_TYPE"].ToString());
            }
            return model;
        }

        public int UPDATE(Lc_Transaction model, string operName)
        {
            string sql = string.Format(@"UPDATE THKQS.Lc_Transaction SET
N_TRANSACTION_STATE='{0}',
C_TRANSACTOR_ID='{1}',
D_TRANSACTION_TIME_END='{2}',
C_TRANSACTION_COMMENT='{3}',
C_TRANSACTOR_SIGNATURE='{4}'
WHERE N_TRANSACTION_ID='{5}'
", model.N_TRANSACTION_STATE, model.C_TRANSACTOR_ID, model.D_TRANSACTION_TIME_END, model.C_TRANSACTION_COMMENT, model.C_TRANSACTOR_SIGNATURE, model.N_TRANSACTION_ID);

            int count = NoQuery(sql, operName);
            return count;
        }

        public int UPDATESTATE(string qids, string bid, string operName)
        {
            string sql = string.Format(@"UPDATE THKQS.Lc_Transaction SET N_TRANSACTION_STATE=1 WHERE N_QUEUE_ID IN {0} AND N_business_id={1}", qids, bid);
            int count = NoQuery(sql, operName);
            return count;
        }

        public int UPDATESTATE(string tranid, int state, string operName)
        {
            string sql = "";
            if (state == 1)
            {
                sql = "UPDATE THKQS.Lc_Transaction SET N_TRANSACTION_STATE=1 WHERE N_TRANSACTION_ID =" + tranid;
            }
            else if (state == 2)
            {
                sql = "UPDATE THKQS.Lc_Transaction SET N_LAST_BACE_TRANSACTION_ID=-1 WHERE N_TRANSACTION_ID =" + tranid;
            }

            int count = NoQuery(sql, operName);
            return count;
        }

        public int ChangeTranUid(string tranid, string tranuid, string operName)
        {
            string sql = "UPDATE THKQS.Lc_Transaction SET C_TRANSACTOR_ID='" + tranuid + "' WHERE N_TRANSACTION_ID =" + tranid;

            int count = NoQuery(sql, operName);
            return count;
        }

        public DataTable GetAgentData(string proid, Sys_User loginuser)
        {
            {
                /*绑定数据 模型
                 * @TRANSACTION_ID Int 办理情况ID
                 * @TRANSACTION_COMMENT String 办理批示
                 * @BEGIN_DATE Datatime 办理业务开始时间
                 * @BUSINESS_ID Int 所属业务ID
                 * @BUSINESS_NAME String 业务名称
                 * @NODE_ID String 节点ID
                 * @NODE_NAME String 节点名称
                 * @QUEUE_ID Int 队列ID
                 * @PROCESS_ID Int 流程ID
                 * @PROCESS_NAME String 流程名称
                 * @DEP_ID Int 部门ID
                 */

                DataTable dt = new DataTable();
                dt.Columns.Add("TRANSACTION_ID");
                dt.Columns.Add("TRANSACTION_COMMENT");
                dt.Columns.Add("BEGIN_DATE");
                dt.Columns.Add("BUSINESS_ID");
                dt.Columns.Add("BUSINESS_NAME");
                dt.Columns.Add("NODE_ID");
                dt.Columns.Add("NODE_NAME");
                dt.Columns.Add("QUEUE_ID");
                dt.Columns.Add("PROCESS_ID");
                dt.Columns.Add("PROCESS_NAME");
                dt.Columns.Add("DEP_ID");
                DataTable _dt;
                {
                    _dt = GetProcessDaiban("-1", "0", 5, loginuser.USER_ID.ToString(), loginuser.USER_COMPANY.ToString(), 0, loginuser.USER_NAME);
                    DataTable dttwo = GetProcessDaiban("-1", "0", 0, loginuser.USER_ID.ToString(), loginuser.USER_COMPANY.ToString(), 0, loginuser.USER_NAME);
                    _dt.Merge(dttwo);

                    DataTable dtMyWt = null;
                    var sql = "SELECT N_ENTRUST_ID, N_PRINCIPAL, N_USER_ID, D_NTRUST_START_DATE, C_ENTRUST_STATE, N_ENTRUST_REMARKS, D_NTRUST_OPERATING_TIME FROM QS_ENTRUST T WHERE T.N_USER_ID='" + loginuser.USER_ID.ToString() + @"' AND T.C_ENTRUST_STATE=0";

                    DataTable dtwt = Query(sql);
                    if (dtwt != null && dtwt.Rows.Count > 0)
                    {
                        foreach (DataRow dr in dtwt.Rows)
                        {
                            if (dtMyWt != null)
                            {
                                string wtSql = "SELECT * FROM ( SELECT LC_TRANSACTION.N_TRANSACTION_ID AS TRANSACTION_ID, LC_TRANSACTION.N_TRANSACTION_COMMENT AS TRANSACTION_COMMENT, LC_TRANSACTION.D_TRANSACTION_TIME_BEGIN AS BEGIN_DATE, LC_TRANSACTION.N_BUSINESS_ID AS BUSINESS_ID, QS_BUSINESS.N_BUSINESS_NAME AS BUSINESS_NAME, LC_NODE.N_NODE_ID AS NODE_ID, LC_NODE.C_NODE_NAME AS NODE_NAME, LC_TRANSACTION.N_QUEUE_ID AS QUEUE_ID, LC_PROCESS.N_PROCESS_ID AS PROCESS_ID, LC_PROCESS.C_PROCESS_NAME AS PROCESS_NAME, NULL AS DEP_ID FROM LC_TRANSACTION INNER JOIN LC_QUEUE ON LC_TRANSACTION.N_QUEUE_ID = LC_QUEUE.N_QUEUE_ID INNER JOIN LC_NODE ON LC_QUEUE.N_NODE_ID = LC_NODE.N_NODE_ID INNER JOIN LC_PROCESS ON LC_TRANSACTION.N_PROCESS_ID = LC_PROCESS.N_PROCESS_ID INNER JOIN QS_BUSINESS ON QS_BUSINESS.N_BUSINESS_ID = LC_TRANSACTION.N_BUSINESS_ID WHERE LC_TRANSACTION.N_QUEUE_ID IN ( SELECT LC_QUEUE.N_QUEUE_ID FROM LC_QUEUE WHERE QS_BUSINESS.N_BUSINESS_STATE = 1 AND INSTR ( ',' || LC_TRANSACTION.C_TRANSACTOR_ID || ',', ',' || '" + dr["N_PRINCIPAL"].ToString() + @"' || ',' ) > 0 OR INSTR ( ',' || LC_QUEUE.C_QUEUE_TRANSACTION_ID || ',', ',' || '" + dr["N_PRINCIPAL"].ToString() + "' || ',' ) > 0 ) AND LC_TRANSACTION.N_TRANSACTION_STATE = 0 ) AND TRUNC (BEGIN_DATE) >= TO_DATE ( '" + dr["D_NTRUST_START_DATE"].ToString() + "', 'YYYY-MM-DD HH24:MI:SS' )";
                                wtSql += " ORDER BY BEGIN_DATE DESC";

                                DataTable dtwt1 = Query(wtSql);
                                dtMyWt.Merge(dtwt1);
                            }
                            else
                            {
                                string wtSql = "SELECT * FROM ( SELECT LC_TRANSACTION.N_TRANSACTION_ID AS TRANSACTION_ID, LC_TRANSACTION.N_TRANSACTION_COMMENT AS TRANSACTION_COMMENT, LC_TRANSACTION.D_TRANSACTION_TIME_BEGIN AS BEGIN_DATE, LC_TRANSACTION.N_BUSINESS_ID AS BUSINESS_ID, QS_BUSINESS.N_BUSINESS_NAME AS BUSINESS_NAME, LC_NODE.N_NODE_ID AS NODE_ID, LC_NODE.C_NODE_NAME AS NODE_NAME, LC_TRANSACTION.N_QUEUE_ID AS QUEUE_ID, LC_PROCESS.N_PROCESS_ID AS PROCESS_ID, LC_PROCESS.C_PROCESS_NAME AS PROCESS_NAME, NULL AS DEP_ID FROM LC_TRANSACTION INNER JOIN LC_QUEUE ON LC_TRANSACTION.N_QUEUE_ID = LC_QUEUE.N_QUEUE_ID INNER JOIN LC_NODE ON LC_QUEUE.N_NODE_ID = LC_NODE.N_NODE_ID INNER JOIN LC_PROCESS ON LC_TRANSACTION.N_PROCESS_ID = LC_PROCESS.N_PROCESS_ID INNER JOIN QS_BUSINESS ON QS_BUSINESS.N_BUSINESS_ID = LC_TRANSACTION.N_BUSINESS_ID WHERE LC_TRANSACTION.N_QUEUE_ID IN ( SELECT LC_QUEUE.N_QUEUE_ID FROM LC_QUEUE WHERE QS_BUSINESS.N_BUSINESS_STATE = 1 AND INSTR ( ',' || LC_TRANSACTION.C_TRANSACTOR_ID || ',', ',' || '" + dr["N_PRINCIPAL"].ToString() + @"' || ',' ) > 0 OR INSTR ( ',' || LC_QUEUE.C_QUEUE_TRANSACTION_ID || ',', ',' || '" + dr["N_PRINCIPAL"].ToString() + @"' || ',' ) > 0 ) AND LC_TRANSACTION.N_TRANSACTION_STATE = 0 ) AND TRUNC (BEGIN_DATE) >= TO_DATE ( '" + dr["D_NTRUST_START_DATE"].ToString() + @"', 'YYYY-MM-DD HH24:MI:SS' )";
                                //if (model.N_QUEUE_ID.ToString() != "0")
                                //{
                                //    wtSql += "  and n_queue_id=" + model.N_QUEUE_ID + " ";
                                //}
                                wtSql += " ORDER BY BEGIN_DATE DESC";

                                dtMyWt = Query(wtSql);
                            }
                        }
                    }
                    if (dtMyWt != null)
                    {
                        _dt.Merge(dtMyWt);
                    }
                }
                foreach (DataRow item in _dt.Rows)
                {
                    var row = dt.NewRow();
                    row["TRANSACTION_ID"] = item["TRANSACTION_ID"];
                    row["TRANSACTION_COMMENT"] = item["TRANSACTION_COMMENT"];
                    row["BEGIN_DATE"] = item["BEGIN_DATE"];
                    row["BUSINESS_ID"] = item["BUSINESS_ID"];
                    row["BUSINESS_NAME"] = item["BUSINESS_NAME"];
                    row["NODE_ID"] = item["NODE_ID"];
                    row["NODE_NAME"] = item["NODE_NAME"];
                    row["QUEUE_ID"] = item["QUEUE_ID"];
                    row["PROCESS_ID"] = item["PROCESS_ID"];
                    row["PROCESS_NAME"] = item["PROCESS_NAME"];
                    row["DEP_ID"] = item["DEP_ID"];
                    dt.Rows.Add(row);
                }

                return dt;
            }
        }

        public DataTable GetAgentDataAll(Sys_User loginuser)
        {
            #region 委托处理

            string sqlwt = @"select distinct n_principal
  from qs_entrust t
 where t.d_ntrust_start_date <= sysdate
   and t.d_ntrust_enddata >= sysdate
   and t.n_user_id = " + loginuser.USER_ID + @"
   and t.n_principal <> " + loginuser.USER_ID;
            DataTable dtwt = Query(sqlwt);

            string str1 = "";
            string str2 = "";
            //查询当前登录人是否有委托
            if (dtwt != null && dtwt.Rows.Count > 0)
            {
                for (int i = 0; i < dtwt.Rows.Count; i++)
                {
                    str1 = " or instr(',' || q.c_queue_transaction_id || ',', '," + dtwt.Rows[0][0].ToString() + @",') > 0 ";
                    str2 = " or instr(',' || t.c_transactor_id || ',', '," + dtwt.Rows[0][0].ToString() + @",') > 0 ";
                }
            }

            #endregion 委托处理

            string[] js = null;

            js = loginuser.USER_ROLEID.Split(',');

            string sql = @"
           select t.n_transaction_id tranid,
               b.n_business_operator_id opid,
               t.n_business_id bid,
               q.n_audit_type n_audit_type,
               n.c_node_name nodename,
               substr(c.cmy_name, 0, 4) c_company_remark,
               c.cmy_id,
               b.n_business_name business,
               p.c_process_name process,
               decode(t.n_transaction_due_state,
                      '0',
                      '未超期',
                      '1',
                      '已超期') due_state,
               t.d_transaction_time_begin,
               t.n_queue_id,
               t.n_process_id
          from lc_transaction t,
               lc_queue       q,
               lc_node        n,
               lc_process     p,
               qs_business    b,
               sys_company    c
         where t.n_queue_id = q.n_queue_id
           and q.n_node_id = n.n_node_id
           and t.n_process_id = p.n_process_id
           and t.n_business_id = b.n_business_id
           and b.n_company_id = c.cmy_id
           and t.n_transaction_state = 0
           and b.n_business_state = 1
           and q.n_tran_type = 0 --人员办理
           and ((q.n_audit_type in (0, 1, 2) and
              ( instr(',' || q.c_queue_transaction_id || ',', '," + loginuser.USER_ID + @",') > 0 " + str1 + @" ) and
               t.n_transaction_id not in
               (select n_transaction_id
                    from lc_audits adt
                   where adt.n_transaction_id = t.n_transaction_id
                     and adt.n_systemuser_id = " + loginuser.USER_ID + @")) or
               (q.n_audit_type in (3, 4, 5) and
              ( instr(',' || t.c_transactor_id || ',', '," + loginuser.USER_ID + @",') > 0 " + str2 + @" )
             ))
           and (b.n_company_id in
               (select CMY_ID
                   from sys_company
                  start with CMY_ID = " + loginuser.USER_COMPANY + @"
                 connect by prior CMY_ID = CMY_PARENTID) or
               q.n_audit_type = 3)";

            #region 角色判断

            if (js != null)
            {
                sql += @"
                               union all
                            select t.n_transaction_id tranid,
               b.n_business_operator_id opid,
               t.n_business_id bid,
               q.n_audit_type n_audit_type,
               n.c_node_name nodename,
               substr(c.cmy_name, 0, 4) c_company_remark,
               c.cmy_id,
               b.n_business_name business,
               p.c_process_name process,
               decode(t.n_transaction_due_state,
                      '0',
                      '未超期',
                      '1',
                      '已超期') due_state,
               t.d_transaction_time_begin,
               t.n_queue_id,
               t.n_process_id
          from lc_transaction t,
               lc_queue       q,
               lc_node        n,
               lc_process     p,
               qs_business    b,
               sys_company    c
         where t.n_queue_id = q.n_queue_id
           and q.n_node_id = n.n_node_id
           and t.n_process_id = p.n_process_id
           and t.n_business_id = b.n_business_id
           and b.n_company_id = c.cmy_id
           and t.n_transaction_state = 0
           and b.n_business_state = 1
           and q.n_tran_type = 1 --岗位办理
                  and ((q.n_audit_type in (0, 1, 2) and (";

                for (int i = 0; i < js.Length; i++)
                {
                    //循环遍历岗位
                    if (i == 0)
                    {
                        sql += @" instr(',' || q.C_POSTS  || ',', '," + js[i] + @",') > 0 ";
                    }
                    else
                    {
                        sql += @" or instr(',' || q.C_POSTS || ',', '," + js[i] + @",') > 0 ";
                    }
                }

                sql += @") and
                           t.n_transaction_id not in
                           (select n_transaction_id
                                from lc_audits adt
                               where adt.n_transaction_id = t.n_transaction_id
                                 and adt.n_systemuser_id = " + loginuser.USER_ID + @")) or
                           (q.n_audit_type in (3, 4, 5) and
                           instr(',' || t.c_transactor_id || ',', '," + loginuser.USER_ID + @",') > 0))
                       and b.n_company_id in
                           (select CMY_ID
                   from sys_company
                  start with CMY_ID = " + loginuser.USER_COMPANY + @"
                 connect by prior CMY_ID = CMY_PARENTID)";
            }

            #endregion 角色判断

            sql = @"select w.*
  from (" + sql + @") w
 group by tranid,
          opid,
          bid,
          n_audit_type,
          nodename,
          business,
          process,
          due_state,
          d_transaction_time_begin,
          n_queue_id,
          c_company_remark,
          cmy_id,
          n_process_id
 order by tranid desc ";

            DataTable dt = Query(sql);

            return dt;
        }

        /// <summary>
        /// 查询正在办理的流程
        /// </summary>
        /// <param name="processId"></param>
        /// <param name="nodeId"></param>
        /// <param name="state"></param>
        /// <param name="logonName"></param>
        /// <returns></returns>
        private DataTable GetProcessDaiban(string processId, string nodeId, decimal type, string sysId, string companyId, decimal state, string logonName)
        {
            string sql = string.Empty;
            if (state == 0)
            {
                //审核方式(0.单人；1.多人审核1人通过及通过；2.多人审核全部通过才通过;3 指定人员办理;4 默认为片区管理员; 5指定特定操作产生的人员)
                if (type == 5)
                {
                    sql = "SELECT LC_TRANSACTION.N_TRANSACTION_ID AS TRANSACTION_ID, LC_TRANSACTION.N_TRANSACTION_COMMENT AS TRANSACTION_COMMENT, LC_TRANSACTION.D_TRANSACTION_TIME_BEGIN AS BEGIN_DATE, LC_TRANSACTION.N_BUSINESS_ID AS BUSINESS_ID, QS_BUSINESS.N_BUSINESS_NAME AS BUSINESS_NAME, LC_NODE.N_NODE_ID AS NODE_ID, LC_NODE.C_NODE_NAME AS NODE_NAME, LC_TRANSACTION.N_QUEUE_ID AS QUEUE_ID, LC_PROCESS.N_PROCESS_ID AS PROCESS_ID, LC_PROCESS.C_PROCESS_NAME AS PROCESS_NAME, NULL AS DEP_ID FROM LC_TRANSACTION INNER JOIN LC_QUEUE ON LC_TRANSACTION.N_QUEUE_ID = LC_QUEUE.N_QUEUE_ID INNER JOIN LC_NODE ON LC_QUEUE.N_NODE_ID = LC_NODE.N_NODE_ID INNER JOIN LC_PROCESS ON LC_TRANSACTION.N_PROCESS_ID = LC_PROCESS.N_PROCESS_ID INNER JOIN QS_BUSINESS ON QS_BUSINESS.N_BUSINESS_ID = LC_TRANSACTION.N_BUSINESS_ID WHERE LC_TRANSACTION.N_QUEUE_ID IN ( SELECT LC_QUEUE.N_QUEUE_ID FROM LC_QUEUE WHERE QS_BUSINESS.N_BUSINESS_STATE = 1 AND INSTR ( ',' || LC_TRANSACTION.C_TRANSACTOR_ID || ',', ',' || '" + sysId + "' || ',' ) > 0 ) AND LC_TRANSACTION.N_TRANSACTION_STATE = 0";
                }
                else
                {
                    sql = "SELECT LC_TRANSACTION.N_TRANSACTION_ID AS TRANSACTION_ID, LC_TRANSACTION.N_TRANSACTION_COMMENT AS TRANSACTION_COMMENT, LC_TRANSACTION.D_TRANSACTION_TIME_BEGIN AS BEGIN_DATE, LC_TRANSACTION.N_BUSINESS_ID AS BUSINESS_ID, QS_BUSINESS.N_BUSINESS_NAME AS BUSINESS_NAME, LC_NODE.N_NODE_ID AS NODE_ID, LC_NODE.C_NODE_NAME AS NODE_NAME, LC_TRANSACTION.N_QUEUE_ID AS QUEUE_ID, LC_PROCESS.N_PROCESS_ID AS PROCESS_ID, LC_PROCESS.C_PROCESS_NAME AS PROCESS_NAME, NULL AS DEP_ID FROM LC_TRANSACTION INNER JOIN LC_QUEUE ON LC_TRANSACTION.N_QUEUE_ID = LC_QUEUE.N_QUEUE_ID INNER JOIN LC_NODE ON LC_QUEUE.N_NODE_ID = LC_NODE.N_NODE_ID INNER JOIN LC_PROCESS ON LC_TRANSACTION.N_PROCESS_ID = LC_PROCESS.N_PROCESS_ID INNER JOIN QS_BUSINESS ON QS_BUSINESS.N_BUSINESS_ID = LC_TRANSACTION.N_BUSINESS_ID WHERE LC_TRANSACTION.N_QUEUE_ID IN ( SELECT LC_QUEUE.N_QUEUE_ID FROM LC_QUEUE WHERE QS_BUSINESS.N_BUSINESS_STATE = 1 AND INSTR ( ',' || LC_QUEUE.C_QUEUE_TRANSACTION_ID || ',', ',' || '" + sysId + @"' || ',' ) > 0 ) AND LC_TRANSACTION.N_TRANSACTION_STATE = 0";
                }
            }
            else
            {
                //查询已办信息
                sql = "SELECT LC_TRANSACTION.N_TRANSACTION_ID AS TRANSACTION_ID, LC_TRANSACTION.N_TRANSACTION_COMMENT AS TRANSACTION_COMMENT, LC_TRANSACTION.D_TRANSACTION_TIME_BEGIN AS BEGIN_DATE, LC_TRANSACTION.N_BUSINESS_ID AS BUSINESS_ID, QS_BUSINESS.N_BUSINESS_NAME AS BUSINESS_NAME, LC_NODE.N_NODE_ID AS NODE_ID, LC_NODE.C_NODE_NAME AS NODE_NAME, LC_TRANSACTION.N_QUEUE_ID AS QUEUE_ID, LC_PROCESS.N_PROCESS_ID AS PROCESS_ID, LC_PROCESS.C_PROCESS_NAME AS PROCESS_NAME, NULL AS DEP_ID FROM LC_TRANSACTION INNER JOIN LC_QUEUE ON LC_TRANSACTION.N_QUEUE_ID = LC_QUEUE.N_QUEUE_ID INNER JOIN LC_NODE ON LC_QUEUE.N_NODE_ID = LC_NODE.N_NODE_ID INNER JOIN LC_PROCESS ON LC_TRANSACTION.N_PROCESS_ID = LC_PROCESS.N_PROCESS_ID INNER JOIN QS_BUSINESS ON QS_BUSINESS.N_BUSINESS_ID = LC_TRANSACTION.N_BUSINESS_ID WHERE LC_TRANSACTION.C_TRANSACTOR_ID IN ( SELECT SYU.USER_ID FROM SYS_USER SYU WHERE SYU.USER_COMPANY = " + companyId + @" ) AND N_BUSINESS_STATE = 2";
            }
            if (processId != "-1")
            {
                sql += " AND LC_PROCESS.N_PROCESS_ID=" + processId + "";
            }
            if (nodeId != "0")
            {
                sql += " AND LC_QUEUE.N_QUEUE_ID=" + nodeId + "";
            }
            sql += " ORDER BY LC_TRANSACTION.D_TRANSACTION_TIME_BEGIN ASC";
            DataTable dt = Query(sql);
            return dt;
        }

        public int GetCountYBSY(string uid, string whr)
        {
            string sql = string.Format(@"SELECT COUNT(*)
  FROM QS_BUSINESS B
 INNER JOIN LC_PROCESS T2
    ON B.N_PROCESS_ID = T2.N_PROCESS_ID
 INNER JOIN THKQS.SYS_USER T3
    ON B.N_BUSINESS_OPERATOR_ID = T3.USER_ID
 WHERE B.N_BUSINESS_ID IN( SELECT DISTINCT(T1.N_BUSINESS_ID)
FROM LC_TRANSACTION T1
WHERE (','||rtrim(T1.C_TRANSACTOR_ID)||',') LIKE '%,{0},%' )" + whr, uid);
            return GetCount(sql);
        }

        public DataTable GetListByPageYBSY(string uid, string whr, string orderby, int startIndex, int endIndex)
        {
            var sql = string.Format(@"SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY " + orderby + @") R, B.N_BUSINESS_ID, B.N_BUSINESS_NAME,
P.C_PROCESS_NAME,
DECODE(B.N_BUSINESS_STATE,0,'注销',1,'办理中',2,'已归档','其它') AS STATE, U.USER_NAME, to_char(B.N_BUSINESS_OPERATOR_TIME, 'yyyy-mm-dd HH24:MI:SS') N_BUSINESS_OPERATOR_TIME, B.N_REMARK
FROM QS_BUSINESS B, LC_PROCESS P, SYS_USER U
WHERE B.N_PROCESS_ID = P.N_PROCESS_ID
AND B.N_BUSINESS_OPERATOR_ID = U.USER_ID
AND B.N_BUSINESS_ID IN
( SELECT DISTINCT T.N_BUSINESS_ID FROM LC_TRANSACTION T WHERE (',' || RTRIM(C_TRANSACTOR_ID) || ',') LIKE '%,{0},%') " + whr + @") TT", uid);
            if (startIndex >= 2)
            {
                sql += string.Format(" WHERE TT.R BETWEEN ({0}-1)*{1}+1 AND {0}*{1}", startIndex, endIndex);
            }
            else
            {
                sql += string.Format(" WHERE TT.R BETWEEN ({0}-1)*{1} AND {0}*{1}", startIndex, endIndex);
            }
            return Query(sql);
        }

        public DataTable GetListByPageYBSY1(string whereStr, string orderbyStr, int? skip, int? take)
        {
            var sql = @"SELECT
	T1.N_BUSINESS_ID,
	T1.N_BUSINESS_NAME,
	T2.C_PROCESS_NAME,
	DECODE (
		T1.N_BUSINESS_STATE,
		0,
		'注销',
		1,
		'办理中',
		2,
		'已归档',
		'其它'
	) AS STATE,
	T3 .USER_NAME,
	to_char(T1.N_BUSINESS_OPERATOR_TIME, 'yyyy-mm-dd HH24:MI:SS') N_BUSINESS_OPERATOR_TIME,
	T1.N_REMARK
FROM
	QS_BUSINESS T1,
	LC_PROCESS T2,
	SYS_USER T3
WHERE
	T1.N_PROCESS_ID = T2.N_PROCESS_ID
AND T1.N_BUSINESS_OPERATOR_ID = T3 .USER_ID";
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }
            if (!string.IsNullOrEmpty(orderbyStr))
            {
                sql += " ORDER BY " + orderbyStr;
            }
            return PageQuery(sql, skip, take);
        }

        public int GetCountYBSY1(string whereStr)
        {
            var sql = @"SELECT
	T1.N_BUSINESS_ID,
	T1.N_BUSINESS_NAME,
	T2.C_PROCESS_NAME,
	DECODE (
		T1.N_BUSINESS_STATE,
		0,
		'注销',
		1,
		'办理中',
		2,
		'已归档',
		'其它'
	) AS STATE,
	T3 .USER_NAME,
	to_char(T1.N_BUSINESS_OPERATOR_TIME, 'yyyy-mm-dd HH24:MI:SS') N_BUSINESS_OPERATOR_TIME,
	T1.N_REMARK
FROM
	QS_BUSINESS T1,
	LC_PROCESS T2,
	SYS_USER T3
WHERE
	T1.N_PROCESS_ID = T2.N_PROCESS_ID
AND T1.N_BUSINESS_OPERATOR_ID = T3 .USER_ID";
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }
            return SqlCount(sql);
        }

        public DataTable GetTranByTid(decimal trid)
        {
            string sql = string.Format(@"SELECT
    t.N_TRANSACTION_ID,
    t.N_PROCESS_ID,
    t.N_QUEUE_ID,
    t.N_business_id,
    n.C_NODE_NAME,
    q.N_QUEUE_NUMBER,
    t.N_Dep_id,
    t.N_TYPE,
    t.C_TRANSACTION_COMMENT
FROM
    THKQS.Lc_Transaction t,
    THKQS.Lc_Queue q,
    THKQS.Lc_Node n
WHERE
    t.N_QUEUE_ID = q.N_QUEUE_ID
    AND t.N_PROCESS_ID = q.N_PROCESS_ID
    AND q.N_NODE_ID = n.N_NODE_ID
    AND (t.N_TRANSACTION_STATE = 0 OR t.N_TRANSACTION_STATE = 2)
    AND t.N_TRANSACTION_ID=" + trid + "");
            DataTable dt = Query(sql);

            return dt;
        }

        /// <summary>
        /// 根据Bid获取节点信息
        /// </summary>
        /// <param name="bid">Bid</param>
        /// <returns>@NodeName,@STATE,@coment,@detailName,@startTime,@endtime,@lasttime,@SIGNATUREID,@dName,@dID</returns>
        public DataTable GetTranByBid(decimal bid)
        {
            var sql = string.Format(@"SELECT
	                                    (
		                                    SELECT
			                                    Lc_Node.C_NODE_NAME
		                                    FROM
			                                    Lc_Node
		                                    WHERE
			                                    Lc_Node.N_NODE_ID IN (
				                                    SELECT
					                                    N_NODE_ID
				                                    FROM
					                                    Lc_Queue
				                                    WHERE
					                                    Lc_Queue.N_QUEUE_ID = T1.N_QUEUE_ID
			                                    )
	                                    ) AS NodeName,
	                                    DECODE (
		                                    T1.N_TRANSACTION_STATE,
		                                    '0',
		                                    '正在办理',
		                                    '1',
		                                    '流转',
		                                    '2',
		                                    '回退',
		                                    '3',
		                                    '逐步重走',
		                                    '一步重走'
	                                    ) AS STATE,
	                                    T1.N_TRANSACTION_COMMENT coment,
	                                    T1.C_TRANSACTOR_ID detailName,
	                                    to_char(T1.D_TRANSACTION_TIME_BEGIN, 'yyyy-mm-dd HH24:MI:SS') startTime,
	                                    to_char(T1.D_TRANSACTION_TIME_END, 'yyyy-mm-dd HH24:MI:SS') endtime,
	                                    to_char(T1.D_TRANSACTION_TIME_DUE, 'yyyy-mm-dd HH24:MI:SS') lasttime,
	                                    T1.C_TRANSACTOR_SIGNATURE SIGNATUREID,
	                                    T3.DEPTNAME AS dName,
	                                    T3.DEPTID AS dID
                                    FROM
	                                    Lc_Transaction T1
	                                    LEFT JOIN SYS_USER T2 ON T1.C_TRANSACTOR_ID = CAST (T2.USER_ID AS VARCHAR2(10))
	                                    LEFT JOIN Sys_Department T3 ON T2.USER_DEPARTMENT = T3.DEPTID
                                    WHERE
	                                    T1.N_business_id = {0}
                                    ORDER BY
	                                    T1.N_TRANSACTION_ID", bid);

            DataTable dt = Query(sql);

            return dt;
        }

        /// <summary>
        /// 通过业务id 获取办理流水
        /// </summary>
        /// <param name="bid"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public DataTable GetTranByBidNew(string bid, string logname)
        {
            string sql = @"
select n.c_node_name,
       t.n_queue_id,
       decode(t.n_transaction_state,
              0,
              '正在办理',
              1,
              '流转',
              2,
              '回退',
              3,
              '逐步重走',
              4,
              '一步重走') state,
       t.c_transactor_id          blr,
       t.n_transaction_comment    blps,
       to_char(t.d_transaction_time_begin, 'yyyy-mm-dd HH24:MI:SS') d_transaction_time_begin,
       to_char(t.d_transaction_time_end, 'yyyy-mm-dd HH24:MI:SS') d_transaction_time_end
  from lc_transaction t, lc_queue q, lc_node n
 where t.n_queue_id = q.n_queue_id
   and q.n_node_id = n.n_node_id
   and t.n_business_id = " + bid + @"
 order by t.n_transaction_id";

            DataTable dt = Query(sql, "", "", logname, logname);
            return dt;
        }

        public bool SelState(string bid, string quens)
        {
            var sql = string.Format(@"SELECT COUNT(*) FROM LC_TRANSACTION
                                        WHERE
	                                        N_BUSINESS_ID = {0} AND
	                                        N_TRANSACTION_STATE = 0 AND
	                                        N_QUEUE_ID IN ({1})", bid, quens);
            if (GetCount(sql) > 0)
                return true;
            return false;
            /*
            string sql = @"SELECT * FROM THKQS.Lc_Transaction t WHERE t.N_business_id=" + bid + @" AND t.N_TRANSACTION_STATE=0 AND t.N_QUEUE_ID IN(" + quens + @") AND t.N_TYPE=0 ";
            DataTable dt = DbHelperSQL.GetDataTable(sql);
            if (dt == null || dt.Rows.Count == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
            */
        }

        public string GetNextNum(string processId, string firstQueue, string operName)
        {
            string nextNum = string.Empty;
            string sql = @"SELECT t.N_QUEUE_ID_RIGHT FROM THKQS.Lc_Condition t WHERE t.N_PROCESS_ID=" + processId + " AND t.N_QUEUE_ID_LEFT=" + firstQueue + "";

            DataTable dt = Query(sql);
            foreach (DataRow dr in dt.Rows)
            {
                nextNum = dr["N_QUEUE_ID_RIGHT"].ToString();
            }

            return nextNum;
        }

        public bool getGoToJx(string N_business_id, string qid)
        {
            string sql = @"SELECT
    tr.N_QUEUE_ID
FROM
	THKQS.Lc_Transaction tr
WHERE
	N_business_id = " + N_business_id + " AND N_QUEUE_ID>" + qid + " AND tr.N_TRANSACTION_STATE = 0  AND tr.N_TYPE=0 ";
            DataTable dt = Query(sql);

            if (dt.Rows.Count > 0)
            {
                return true;
            }

            return false;
        }

        public string getFqr(decimal bid, decimal pid, decimal qid)
        {
            string sql = @" SELECT C_TRANSACTOR_ID FROM THKQS.Lc_Transaction tr WHERE tr.N_business_id=" + bid + @" AND tr.N_PROCESS_ID=" + pid + " AND tr.N_QUEUE_ID=" + qid + " ORDER BY tr.N_TRANSACTION_ID DESC";
            DataTable dt = Query(sql);
            return dt.Rows[0][0].ToString();
        }

        public DataTable GetKHWCT(string bid, string dep, string uid, string qid)
        {
            string sql = @"SELECT
    N_TRANSACTION_STATE
FROM
	THKQS.Lc_Transaction tr
WHERE
	tr.N_business_id = " + bid + " AND tr.N_Dep_id=" + dep + " AND tr.C_TRANSACTOR_ID=" + uid + " AND (tr.N_TRANSACTION_STATE=0 OR tr.N_TRANSACTION_STATE=2) AND tr.N_QUEUE_ID=" + qid + " AND tr.N_TYPE=1";
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetKHWCT2(string bid, string dep, string uid, string qid)
        {
            string sql = @"SELECT
    N_TRANSACTION_STATE
FROM
	THKQS.Lc_Transaction tr
WHERE
	tr.N_business_id = " + bid + " AND tr.N_Dep_id=" + dep + " AND ','+rtrim(tr.C_TRANSACTOR_ID)+',' like '," + uid + ',' + "%' AND tr.N_TRANSACTION_STATE=1 AND tr.N_QUEUE_ID>=" + qid;
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetKHWCT3(string bid, string dep, string uid, string qid)
        {
            string sql = @"SELECT
    N_TRANSACTION_STATE
FROM
	THKQS.Lc_Transaction tr
WHERE
	tr.N_business_id = " + bid + " AND tr.N_Dep_id=" + dep + " AND ','+rtrim(tr.C_TRANSACTOR_ID)+',' like '," + uid + ',' + "%' AND tr.N_TRANSACTION_STATE=1 AND tr.N_QUEUE_ID=" + qid;
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetKHWCT4(string bid, string dep, string qid)
        {
            string sql = @"SELECT
    N_TRANSACTION_STATE
FROM
	THKQS.Lc_Transaction tr
WHERE
	tr.N_business_id = " + bid + " AND tr.N_Dep_id=" + dep + " AND tr.N_TRANSACTION_STATE=1 AND tr.N_QUEUE_ID=" + qid;
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetKHWCT5(string bid, string dep, string qid)
        {
            string sql = @"SELECT
    N_TRANSACTION_STATE
FROM
	THKQS.Lc_Transaction tr
WHERE
	tr.N_business_id = " + bid + " AND tr.N_Dep_id=" + dep + " AND tr.N_TRANSACTION_STATE=0 AND tr.N_LAST_BACE_TRANSACTION_ID=-1 AND tr.N_QUEUE_ID=" + qid;
            DataTable dt = Query(sql);
            return dt;
        }

        public bool Back(string bid, string dep, string uid, string name, string yj, int qid)
        {
            int count = 0;
            bool back = true;

            string sql = @"SELECT *
FROM
	THKQS.Lc_Transaction tr
WHERE
	N_business_id = " + bid + " AND N_Dep_id=" + dep + " AND C_TRANSACTOR_ID=" + uid + " AND N_TYPE=1 AND (N_TRANSACTION_STATE==0 OR N_TRANSACTION_STATE==2)";
            DataTable dt = Query(sql);

            if (dt.Rows.Count > 0)
            {
                back = false;
            }

            if (back)
            {
                Lc_Transaction nextModel = new Lc_Transaction();
                nextModel.C_TRANSACTOR_ID = uid;
                nextModel.N_PROCESS_ID = 109;
                nextModel.N_business_id = decimal.Parse(bid);
                nextModel.N_QUEUE_ID = qid;
                nextModel.N_TRANSACTION_STATE = 2;
                nextModel.C_TRANSACTION_COMMENT = yj;
                nextModel.D_TRANSACTION_TIME_BEGIN = DateTime.Now;
                nextModel.D_TRANSACTION_TIME_END = Convert.ToDateTime("1900-01-01 00:00:00");
                nextModel.D_TRANSACTION_TIME_DUE = Convert.ToDateTime("1900-01-01 00:00:00");
                nextModel.N_TRANSACTION_DUE_STATE = 0;
                nextModel.C_TRANSACTOR_SIGNATURE = "";
                //  nextModel.N_Dep_id = decimal.Parse(dep);
                nextModel.N_LAST_BACE_TRANSACTION_ID = 0;
                // nextModel.N_TYPE = 1;

                count = Insert(nextModel, name);
            }

            if (count == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool Back2(string bid, string dep, string name, int qid)
        {
            int count = 0;

            Sys_DepartmentBLL jpd = new Sys_DepartmentBLL();
            DataTable dt1 = jpd.GetZHRY(dep);

            string sql = @"SELECT *
FROM
	THKQS.Lc_Transaction tr
WHERE
	tr.N_business_id = " + bid + " AND tr.N_Dep_id=" + dep + " AND tr.N_TYPE=0";
            DataTable dt2 = Query(sql);

            sql = @"SELECT *
FROM
	THKQS.Lc_Transaction tr
WHERE
	tr.N_business_id = " + bid + " AND tr.N_Dep_id=" + dep + " AND tr.N_QUEUE_ID=184 AND (tr.N_TRANSACTION_STATE=0 OR tr.N_TRANSACTION_STATE=2) AND tr.N_TYPE=0";
            DataTable dt3 = Query(sql);

            if (dt3.Rows.Count == 0)
            {
                Lc_Transaction nextModel = new Lc_Transaction();
                nextModel.C_TRANSACTOR_ID = dt1.Rows[0]["USER_ID"].ToString();
                nextModel.N_PROCESS_ID = decimal.Parse(dt2.Rows[0]["N_PROCESS_ID"].ToString());
                nextModel.N_business_id = decimal.Parse(bid);
                nextModel.N_QUEUE_ID = qid;
                nextModel.N_TRANSACTION_STATE = 2;
                nextModel.C_TRANSACTION_COMMENT = "";
                nextModel.D_TRANSACTION_TIME_BEGIN = DateTime.Now;
                nextModel.D_TRANSACTION_TIME_END = Convert.ToDateTime("1900-01-01 00:00:00");
                nextModel.D_TRANSACTION_TIME_DUE = Convert.ToDateTime("1900-01-01 00:00:00");
                nextModel.N_TRANSACTION_DUE_STATE = 0;
                nextModel.C_TRANSACTOR_SIGNATURE = "";
                //  nextModel.N_Dep_id = decimal.Parse(dep);
                nextModel.N_LAST_BACE_TRANSACTION_ID = 0;
                //   nextModel.N_TYPE = 0;

                count += Insert(nextModel, name);
            }

            if (count >= 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool Back3(string bid, string dep, string uid, string name, string yj, int qid)
        {
            int count = 0;
            bool back = true;

            string sql = @"SELECT *
FROM
	THKQS.Lc_Transaction tr
WHERE
	N_business_id = " + bid + " AND N_Dep_id=" + dep + " AND C_TRANSACTOR_ID=" + uid + " AND N_TYPE=1  AND (N_TRANSACTION_STATE==0 OR N_TRANSACTION_STATE==2)";
            DataTable dt = Query(sql);

            if (dt.Rows.Count > 0)
            {
                back = false;
            }

            if (back)
            {
                Lc_Transaction nextModel = new Lc_Transaction();
                nextModel.C_TRANSACTOR_ID = uid;
                nextModel.N_PROCESS_ID = 114;
                nextModel.N_business_id = decimal.Parse(bid);
                nextModel.N_QUEUE_ID = qid;
                nextModel.N_TRANSACTION_STATE = 2;
                nextModel.C_TRANSACTION_COMMENT = yj;
                nextModel.D_TRANSACTION_TIME_BEGIN = DateTime.Now;
                nextModel.D_TRANSACTION_TIME_END = Convert.ToDateTime("1900-01-01 00:00:00");
                nextModel.D_TRANSACTION_TIME_DUE = Convert.ToDateTime("1900-01-01 00:00:00");
                nextModel.N_TRANSACTION_DUE_STATE = 0;
                nextModel.C_TRANSACTOR_SIGNATURE = "";
                // nextModel.N_Dep_id = decimal.Parse(dep);
                nextModel.N_LAST_BACE_TRANSACTION_ID = 0;
                //   nextModel.N_TYPE = 1;

                Insert(nextModel, name);
            }

            if (count == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool Back4(string bid, string dep, string name, int qid)
        {
            int count = 0;

            Sys_DepartmentBLL jpd = new Sys_DepartmentBLL();
            DataTable dt1 = jpd.GetZHRY(dep);

            string sql = @"SELECT *
FROM
	THKQS.Lc_Transaction tr
WHERE
	tr.N_business_id = " + bid + " AND tr.N_Dep_id=" + dep + " AND tr.N_TYPE=0";
            DataTable dt2 = Query(sql);

            sql = @"SELECT *
FROM
	THKQS.Lc_Transaction tr
WHERE
	tr.N_business_id = " + bid + " AND tr.N_Dep_id=" + dep + " AND tr.N_QUEUE_ID=208 AND (tr.N_TRANSACTION_STATE=0 OR tr.N_TRANSACTION_STATE=2) AND tr.N_TYPE=0";
            DataTable dt3 = Query(sql);

            if (dt3.Rows.Count == 0)
            {
                Lc_Transaction nextModel = new Lc_Transaction();
                nextModel.C_TRANSACTOR_ID = dt1.Rows[0]["USER_ID"].ToString();
                nextModel.N_PROCESS_ID = decimal.Parse(dt2.Rows[0]["N_PROCESS_ID"].ToString());
                nextModel.N_business_id = decimal.Parse(bid);
                nextModel.N_QUEUE_ID = qid;
                nextModel.N_TRANSACTION_STATE = 2;
                nextModel.C_TRANSACTION_COMMENT = "";
                nextModel.D_TRANSACTION_TIME_BEGIN = DateTime.Now;
                nextModel.D_TRANSACTION_TIME_END = Convert.ToDateTime("1900-01-01 00:00:00");
                nextModel.D_TRANSACTION_TIME_DUE = Convert.ToDateTime("1900-01-01 00:00:00");
                nextModel.N_TRANSACTION_DUE_STATE = 0;
                nextModel.C_TRANSACTOR_SIGNATURE = "";
                //   nextModel.N_Dep_id = decimal.Parse(dep);
                nextModel.N_LAST_BACE_TRANSACTION_ID = 0;
                // nextModel.N_TYPE = 0;

                count += Insert(nextModel, name);
            }

            if (count >= 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public int GetRecordCount(string bid)
        {
            var sql = string.Format("SELECT COUNT(*) FROM THKQS.LC_TRANSACTION WHERE N_BUSINESS_ID = {0}", bid);
            return GetCount(sql);
        }

        /// <summary>
        /// 通过Bid查询流程情况
        /// </summary>
        /// <param name="bid">Bid</param>
        /// <param name="orderby">排序</param>
        /// <param name="startIndex">开始行索引</param>
        /// <param name="endIndex">结束行索引</param>
        /// <returns>@C_BUSINESS_NAME,@DEPTNAME,@N_Dep_id,@N_BUSINESS_ID,@R</returns>
        [Obsolete("orderby: 暂时默认 N_business_id,DEPTNAME,填入其它值无效")]
        public DataTable GetDFListByPage(string bid, string orderby, int startIndex, int endIndex)
        {
            var sql = string.Format(@"SELECT * FROM
                                    (
	                                    SELECT T2.N_BUSINESS_NAME AS C_BUSINESS_NAME,T4.DEPTNAME,T3.USER_DEPARTMENT AS N_Dep_id,T1.N_BUSINESS_ID,ROW_NUMBER () OVER (ORDER BY T1.N_business_id,T4.DEPTNAME) R FROM
	                                    LC_TRANSACTION T1
	                                    LEFT JOIN QS_BUSINESS T2 ON T1.N_BUSINESS_ID = T2.N_BUSINESS_ID
	                                    LEFT JOIN SYS_USER T3 ON T2.N_BUSINESS_OPERATOR_ID = T3.USER_ID
	                                    LEFT JOIN SYS_DEPARTMENT T4 ON T3.USER_DEPARTMENT = T4.DEPTID
	                                    WHERE
		                                    T3.USER_DEPARTMENT <> 200 AND
		                                    T3.USER_DEPARTMENT <> 300 AND
		                                    T3.USER_DEPARTMENT <> 0 AND
		                                    T1.N_BUSINESS_ID = {0}
	                                    GROUP BY
		                                    T1.N_BUSINESS_ID,
		                                    T2.N_BUSINESS_NAME,
		                                    T4.DEPTNAME,
		                                    T3.USER_DEPARTMENT
                                    )	T
                                    WHERE T.R BETWEEN {1} AND {2}", bid, startIndex, endIndex);
            /*
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT * FROM (SELECT
	b.C_BUSINESS_NAME,d.DEPTNAME,t.N_Dep_id,t.N_business_id,ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r
FROM
	THKQS.Sys_Department d,
    THKQS.Lc_Transaction t,
	THKQS.QS_Business b
WHERE
     t.N_Dep_id = d.DEPTID
     AND b.N_business_id = t.N_business_id
     AND t.N_TYPE=0 AND t.N_Dep_id<>200 AND t.N_Dep_id<>300 AND t.N_Dep_id<>0 AND t.N_business_id=" + bid);
            strSql.Append(@"
GROUP BY
	t.N_business_id,
	b.C_BUSINESS_NAME,
    d.DEPTNAME,
    t.N_Dep_id) as TT");
            if (startIndex >= 2)
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1}+1 AND {0}*{1}", startIndex, endIndex);
            }
            else
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1} AND {0}*{1}", startIndex, endIndex);
            }

            return DbHelperSQL.Query(strSql.ToString());
            */
            return Query(sql);
        }

        public string getJD(string N_business_id, string N_Dep_id, int qid)
        {
            string sql = @"SELECT
    tr.N_QUEUE_ID
FROM
	THKQS.Lc_Transaction tr
WHERE
	N_business_id = " + N_business_id + " AND N_Dep_id=" + N_Dep_id + " AND (tr.N_TRANSACTION_STATE = 0 OR tr.N_TRANSACTION_STATE = 2) AND tr.N_TYPE=0 AND tr.N_Dep_id>0 AND tr.N_QUEUE_ID>=" + qid + " ORDER BY tr.N_QUEUE_ID DESC";
            DataTable dt = Query(sql);

            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0][0].ToString() == qid.ToString())
                {
                    return "待初审";
                }
                if (dt.Rows[0][0].ToString() == (qid + 1).ToString())
                {
                    return "待审查";
                }
                if (dt.Rows[0][0].ToString() == (qid + 2).ToString())
                {
                    return "待审核";
                }
            }

            DataTable dtb = new Qs_BusinessBLL().getFindById(N_business_id);
            if (dtb.Rows[0]["n_business_state"].ToString() == "2")
            {
                return "结束";
            }
            else
            {
                return "待审批";
            }
        }

        public string getJD2(string N_business_id, string N_Dep_id, int qid)
        {
            string sql = @"SELECT
    tr.N_QUEUE_ID
FROM
	THKQS.Lc_Transaction tr
WHERE
	N_business_id = " + N_business_id + " AND N_Dep_id=" + N_Dep_id + " AND (tr.N_TRANSACTION_STATE = 0 OR tr.N_TRANSACTION_STATE = 2) AND tr.N_TYPE=0 AND tr.N_Dep_id>0 AND tr.N_QUEUE_ID>=" + qid + "ORDER BY tr.N_QUEUE_ID DESC";
            DataTable dt = Query(sql);

            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0][0].ToString() == qid.ToString())
                {
                    return "待初审";
                }
                if (dt.Rows[0][0].ToString() == (qid + 1).ToString())
                {
                    return "待审查";
                }
                if (dt.Rows[0][0].ToString() == (qid + 2).ToString())
                {
                    return "待审核";
                }
                if (dt.Rows[0][0].ToString() == (qid + 3).ToString())
                {
                    return "待初审";
                }
                if (dt.Rows[0][0].ToString() == (qid + 4).ToString())
                {
                    return "待审查";
                }
                if (dt.Rows[0][0].ToString() == (qid + 5).ToString())
                {
                    return "待审核";
                }
            }

            DataTable dtb = new Qs_BusinessBLL().getFindById(N_business_id);
            if (dtb.Rows[0]["n_business_state"].ToString() == "2")
            {
                return "结束";
            }
            else
            {
                return "待审批";
            }
        }

        public DataTable GetPid(string bid)
        {
            string sql = @"SELECT t.N_PROCESS_ID FROM THKQS.Lc_Transaction t WHERE t.N_business_id=" + bid;
            DataTable dt = Query(sql);
            return dt;
        }

        public int GetTranCount(string whereStr)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT count(1) FROM (SELECT b.C_BUSINESS_NAME,u.USER_NAME,u.USER_ROLEID,d.DEPTNAME,CASE N_LAST_BACE_TRANSACTION_ID WHEN -1 THEN '是' ELSE '否' END 跳过审核
FROM THKQS.Lc_Transaction t,THKQS.QS_Business b,THKQS.Sys_User u,THKQS.Sys_Department d WHERE t.N_business_id=b.N_business_id AND t.C_TRANSACTOR_ID=cast(u.USER_ID as varchar) AND t.N_Dep_id=d.DEPTID AND t.N_TRANSACTION_STATE=0 " + whereStr + ") TT"
);
            return GetCount(strSql.ToString());
        }

        public DataTable GetTranPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT * FROM (SELECT t.N_TRANSACTION_ID 代办ID,b.C_BUSINESS_NAME 业务名称,u.USER_NAME 办理人,u.USER_ROLEID,d.DEPTNAME 所属单位,CASE N_LAST_BACE_TRANSACTION_ID WHEN -1 THEN '是' ELSE '否' END 跳过审核,ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r
FROM THKQS.Lc_Transaction t,THKQS.QS_Business b,THKQS.Sys_User u,THKQS.Sys_Department d WHERE t.N_business_id=b.N_business_id AND t.C_TRANSACTOR_ID=cast(u.USER_ID as varchar) AND t.N_Dep_id=d.DEPTID AND t.N_TRANSACTION_STATE=0 " + whereStr + " ) as TT");
            if (startIndex >= 2)
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1}+1 AND {0}*{1}", startIndex, endIndex);
            }
            else
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1} AND {0}*{1}", startIndex, endIndex);
            }
            return Query(strSql.ToString());
        }

        /// <summary>
        /// 流程流转
        /// </summary>
        /// <param name="model"></param>
        /// <param name="nextCon">下一步逻辑条件</param>
        /// <param name="zdSysid">指定人员</param>
        /// <param name="n_jumpnode">驳回时 跳转的节点</param>
        /// <param name="type">1 通过 2 驳回 3废弃</param>
        /// <returns></returns>
        public string PassTran(Lc_Transaction model, string userid, string nextCon, string zdSysid, string n_jumpnode, int type)
        {
            ServiceHelper myservice = new ServiceHelper();

            DataTable dt = new DataTable("orasptab");
            dt.Columns.Add("pname", Type.GetType("System.String"));
            dt.Columns.Add("pvalue", Type.GetType("System.String"));
            dt.Columns.Add("dir", Type.GetType("System.String"));
            dt.Columns.Add("size", Type.GetType("System.String"));

            dt.Rows.Add(new object[] { "N_TransactionID", model.N_TRANSACTION_ID.ToString(), "1", 20 });

            dt.Rows.Add(new object[] { "n_conditionid", nextCon, "1", 20 });

            dt.Rows.Add(new object[] { "n_systemuserid", userid, "1", 20 });

            dt.Rows.Add(new object[] { "c_zduserids", zdSysid, "1", 100 });

            dt.Rows.Add(new object[] { "c_tcomment", model.C_TRANSACTION_COMMENT, "1", 30 });

            dt.Rows.Add(new object[] { "n_type", type, "1", 30 });

            dt.Rows.Add(new object[] { "n_jumpnode", n_jumpnode, "1", 30 });

            dt.Rows.Add(new object[] { "n_Bacemode", model.N_TRANSACTION_STATE.ToString(), "1", 30 });

            dt.Rows.Add(new object[] { "SignatureId", model.C_TRANSACTOR_SIGNATURE, "1", 30 });

            //输出参数
            dt.Rows.Add(new object[] { "c_err", "", "0", 200 });

            string addstate = myservice.ExcuteProcEX("pkg_TranWork.Proc_Pass", dt, "Proc_Pass", "PassTran", "流程流转", 9);

            return addstate;
        }

        /// <summary>
        /// 得到办理过的内容
        /// </summary>
        /// <param name="businessId"></param>
        /// <returns></returns>
        public DataTable GetTran(decimal businessId)
        {
            string sql = string.Format(@"select (select Lc_NODE.C_NODE_NAME from Lc_NODE
         where Lc_NODE.N_NODE_ID in
               (select N_NODE_ID from LC_QUEUE
                 where Lc_QUEUE.N_QUEUE_ID = lc_transaction.n_queue_id)) NodeName,
                decode(lc_transaction.N_TRANSACTION_STATE, 0,'正在办理',1,'流转',2,'回退',3,'回跳',4,'前跳') state,
       lc_transaction.N_TRANSACTION_COMMENT coment,

       lc_transaction.D_TRANSACTION_TIME_BEGIN startTime,
       lc_transaction.D_TRANSACTION_TIME_END endtime,
       lc_transaction.D_TRANSACTION_TIME_DUE lasttime,
       lc_transaction.C_TRANSACTOR_SIGNATURE signatureid,
      case when  lc_transaction.n_transaction_state = 0 then
         to_char('')
         else
            lc_transaction.c_transactor_id
      end c_transactor_id
  from lc_transaction where
  lc_transaction.n_business_id=" + businessId + @" order by lc_transaction.N_TRANSACTION_ID");

            DataTable dt = Query(sql);

            return dt;
        }
    }
}